package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.glowmee.cmd.CommonCodeCMD;

/** 공통코드의 기능을 구현하는 클래스 */
public class CommonCodeDAO {
	
	static Logger logger = Logger.getLogger(CommonCodeDAO.class);
	
	private static CommonCodeDAO instance = null;
	
	DBHandler db = null;
	
	public static CommonCodeDAO getInstance() {
		if (instance == null) {
			instance = new CommonCodeDAO();
		}
		return instance;
	}
	
	public CommonCodeDAO() {
		db = DBHandler.getInstance();
	}
	
	/**
	 * 공통코드 조회
	 * @param idCommon
	 * @return
	 * @throws Exception
	 */
	public CommonCodeCMD getCommonCode(int idCommon) throws Exception {
		logger.debug("[getCommonCode] ---------- Method Start ----------");
		logger.debug("[getCommonCode][PARAM] idCommon=" + idCommon);
		
		CommonCodeCMD result = null;
		String sql = "SELECT idCommon, parentId, commonText, sortKey, isDisplay, create_date, modified_date "
				+ "FROM commoncode WHERE idCommon=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idCommon);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					int parentId = rs.getInt(2);
					String commonText = rs.getString(3);
					int sortKey = rs.getInt(4);
					int isDisplay = rs.getInt(5);
					String create_date = rs.getString(6);
					String modified_date = rs.getString(7);
					result = new CommonCodeCMD(num, parentId, commonText, sortKey, isDisplay, create_date, modified_date);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getCommonCode] " + e.getLocalizedMessage());
			logger.error("[getCommonCode] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getCommonCode][RETURN] result=" + result.toString());
		}
		logger.debug("[getCommonCode] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 공통코드 리스트 조회
	 * @param order
	 * @param where
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CommonCodeCMD> getCommonCodeList(String order, String where) throws Exception {
		logger.debug("[getCommonCodeList] ---------- Method Start ----------");
		logger.debug("[getCommonCodeList][PARAM] order=" + order);
		logger.debug("[getCommonCodeList][PARAM] where=" + where);
		
		ArrayList<CommonCodeCMD> result = null;
		
		String sql = null;
		if (order == null && where == null) {
			sql = "SELECT idCommon, parentId, commonText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM commoncode";
		} else if (order != null && where != null) {
			sql = "SELECT idCommon, parentId, commonText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM commoncode "
					+ "WHERE " + where + " "
					+ "ORDER BY " + order;
		} else if (order != null) {
			sql = "SELECT idCommon, parentId, commonText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM commoncode "
					+ "ORDER BY " + order;
		} else if (where != null) {
			sql = "SELECT idCommon, parentId, commonText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM commoncode "
					+ "WHERE " + where;
		}
		
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<CommonCodeCMD>();
				
				while(rs.next()) {
					int num = rs.getInt(1);
					int parentId = rs.getInt(2);
					String commonText = rs.getString(3);
					int sortKey = rs.getInt(4);
					int isDisplay = rs.getInt(5);
					String create_date = rs.getString(6);
					String modified_date = rs.getString(7);
					CommonCodeCMD item = new CommonCodeCMD(num, parentId, commonText, sortKey, isDisplay, create_date, modified_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getCommonCodeList] " + e.getLocalizedMessage());
			logger.error("[getCommonCodeList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getCommonCodeList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getCommonCodeList] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 공통코드 리스트 조회
	 * @param order
	 * @param where
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CommonCodeCMD> getCommonCodeList(String sql) throws Exception {
		logger.debug("[getCommonCodeList] ---------- Method Start ----------");
		logger.debug("[getCommonCodeList][PARAM] sql=" + sql);
		
		ArrayList<CommonCodeCMD> result = null;
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<CommonCodeCMD>();
				
				while(rs.next()) {
					int num = rs.getInt(1);
					int parentId = rs.getInt(2);
					String commonText = rs.getString(3);
					int sortKey = rs.getInt(4);
					int isDisplay = rs.getInt(5);
					String create_date = rs.getString(6);
					String modified_date = rs.getString(7);
					CommonCodeCMD item = new CommonCodeCMD(num, parentId, commonText, sortKey, isDisplay, create_date, modified_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getCommonCodeList] " + e.getLocalizedMessage());
			logger.error("[getCommonCodeList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getCommonCodeList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getCommonCodeList] ---------- Method End ----------");
		
		return result;
	}
	
	public int getCommon(String where) throws SQLException {
		logger.debug("[getCommon] ---------- Method Start ----------");
		logger.debug("[getCommon][PARAM] where=" + where);
		
		int result = 0;

		String sql = "SELECT idCommon FROM commoncode WHERE commonText=" + where;
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if(rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getCommon] " + e.getLocalizedMessage());
			logger.error("[getCommon] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		logger.debug("[getCommon] ---------- Method End ----------");
		return result;
	}

}
